package com.gljx.web.controller;

import com.gljx.constant.Constant;
import com.gljx.util.*;
import com.gljx.web.Entity.*;
import com.gljx.web.service.*;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.xvolks.jnative.exceptions.NativeException;
import sun.misc.BASE64Decoder;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @Autowired
    private DepartmentService departmentService;

    @Autowired
    private TrainTypeService trainTypeService;

    @Autowired
    private ClazzService clazzService;

    @Autowired
    private CustomLogService customLogService;

    @Autowired
    private StudentPhotoService studentPhotoService;

    @Autowired
    private TimingChargeService timingChargeService;

    @Autowired
    private SystemParameterService systemParameterService;


    //日期格式化
    SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyyMMddHHmmss");


    /**
     * 新增学员页面
     *
     * @param request
     * @return 需要跳转页面
     * @createTime 2017-11-1 11:20 am
     * @author zyq.
     * @updateTime 2017-11-4 10:10 am
     * @author zyq.
     */
    @RequestMapping(value = "/addStudent")
    public String addStudent(HttpServletRequest request) {
        HttpSession session = request.getSession();
        User user = (User) session.getAttribute("user");                                        //获取已经登录用户数据
        Department department = departmentService.queryAdminByFid(user.getFcompanyid());      //获取登录用户组织ID
        List<TrainType> trainTypes = trainTypeService.selTrainType();
        List<BigCarData> bigCarDatas = studentService.selectBigCarData();
        List<Clazz> clazzs = clazzService.selectByCompanyID(user.getFcompanyid());
        List<String> carTypes = customLogService.queryCarTypeAll();
        session.setAttribute("department", department);
        request.setAttribute("clazzs", clazzs);
        request.setAttribute("carTypes", carTypes);
        request.setAttribute("bigCarDatas", bigCarDatas);
        request.setAttribute("trainTypes", trainTypes);
        request.setAttribute("flag", 1);                                                     //是否为新增记录
        return "backHome/student/detailStudent";
    }

    /**
     * 根据FID查询培训类型
     *
     * @param id
     * @return
     * @createTime 2017-11-3 11:20 am
     * @author zyq.
     */
    @ResponseBody
    @RequestMapping(value = "/selTrainTypeById")
    public List<TrainType> selTrainTypeById(String id) {
        List<TrainType> trainTypes = new ArrayList<>();
        String[] ids = id.split(",");
        for (int i = 0; i < ids.length; i++) {                                           //获取多个ID
            TrainType trainType = trainTypeService.selTrainTypeById(ids[i]);
            trainTypes.add(trainType);
        }
        return trainTypes;
    }

    /**
     * 根据班级名称查询班级
     *
     * @param name
     * @return
     * @createTime 2017-11-6 10:20 am
     * @author zyq.
     */
    @RequestMapping(value = "/selClazzByName")
    @ResponseBody
    public List<Clazz> selClazzByName(String name,HttpServletRequest request) {
        HttpSession session = request.getSession();
        User user = (User) session.getAttribute("user");
        List<Clazz> clazzs = clazzService.selectClazzByName(name,user.getFcompanyid());
        return clazzs;
    }

    /**
     * 查询学员入学登记记录，时间缺省为入学日期-入学日期前几天
     *
     * @param request
     * @return
     * @throws Exception
     * @createTime 2017-11-1 9:29 am
     * @author zyq.
     */
    @RequestMapping(value = "/selStudent/look.action")
    public String selStudent(HttpServletRequest request) throws Exception {
        HttpSession session = request.getSession();
        User user = (User) session.getAttribute("user");                                   //获取已经登录用户数据
        Department department = departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
        Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
        ca.setTime(new Date()); // 设置时间为当前时间
        ca.add(Calendar.DATE, -1);// 月份减1
        Date resultDate = ca.getTime(); // 结果
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String beginTime = sdf.format(resultDate);
        String endTime = sdf.format(new Date());
        PageBean<Student> pBean = getPageBean(request, department.getFid(), beginTime, endTime);        //分页数据
        /*String fnumber = customLogService.selectFunmber("ct_ds_student","X",GetUserSession.getUserSession(request));
        System.out.println(fnumber);*/
        List<Student> sList = pBean.getBeanList();
        List<Student> stateList = EnumListFactory.createEnumList();
       //获取驾校对应部门
        List<Department> departments=departmentService.queryAdminByController(user.getFcompanyid());
        //		rhc  信息总条数
        String messageAmount="共有"+pBean.getTr()+"条信息";
        request.setAttribute("beginTime", beginTime);
        request.setAttribute("endTime", endTime);
        request.getSession().setAttribute("stateList", stateList);
        request.setAttribute("sList", sList);
        request.setAttribute("pb", pBean);
        //		rhc  信息总条数
        request.setAttribute("messageAmount",messageAmount);
        //部门信息
        request.setAttribute("DepartmentList",departments);
        return "backHome/student/studentList";                                                   //返回页面
    }


    /**
     * 根据FID查询学员
     *
     * @param fid
     * @return
     * @throws IOException
     * @createTime 2017-11-8 4:50 pm
     * @author zyq.
     */
    @RequestMapping(value = "/selectStudentByFid")
    public String selectStudentByFid(String fid, HttpServletResponse response, HttpServletRequest request) throws IOException {
        HttpSession session = request.getSession();
        fid = fid.replace(" ", "+");
        session.setAttribute("fid", fid);
        Student student = studentService.selectStudentById(fid);                                    //根据FID查询学员
        List<CustomLog> customLogs = customLogService.selectCustomLogById(fid);
        List<TrainType> trainTypes = trainTypeService.selTrainType();
        List<BigCarData> bigCarDatas = studentService.selectBigCarData();
        List<String> carTypes = customLogService.queryCarTypeAll();
        List<Clazz> clazzs = clazzService.selectByCompanyID(GetUserSession.getUserSession(request));
        //获取驾校对应部门
        List<Department> departments=departmentService.queryAdminByController(//获取驾校对应部门
               GetUserSession.getUserSession(request));
        request.setAttribute("trainTypes", trainTypes);
        request.setAttribute("bigCarDatas", bigCarDatas);
        request.setAttribute("carTypes", carTypes);
        request.setAttribute("student", student);
        request.setAttribute("logs", customLogs);
        request.setAttribute("clazzs", clazzs);
        request.setAttribute("flag", 2);
        //部门信息
        request.setAttribute("DepartmentList",departments);
        return "backHome/student/detailStudent";
    }

    /**
     * 查看学员图片
     *
     * @param response
     * @param request
     * @throws IOException
     * @createTime 2017-11-8 2:20 pm
     * @author zyq.
     */
    @RequestMapping(value = "/selectStudentPhoto")
    public void selectStudentPhoto(HttpServletResponse response, HttpServletRequest request) throws IOException {
        HttpSession session = request.getSession();
        String fid = (String) session.getAttribute("fid");
        StudentPhoto studentPhoto = studentPhotoService.selectStudentPhotoByStudentId(fid);       //根据fid查询头像
        if (studentPhoto != null) {
            if (studentPhoto.getFimagedata() != null) {                            //如果头像存在
                InputStream buffin = new ByteArrayInputStream(studentPhoto.getFimagedata());            //读取输入流
                BufferedImage image = ImageIO.read(buffin);
                /*byte b[]=studentPhoto.getFimagedata();
                ByteArrayInputStream in = new ByteArrayInputStream(b);*/
                OutputStream os = response.getOutputStream();
                /*ByteArrayOutputStream f=new ByteArrayOutputStream();*/
                /*f.write(b);*/
                ImageIO.write(image, "PNG", os);                                                    //向页面输出字节数组
                os.flush();
                os.close();
            } else {

            }
        } else {

        }
        /*f.flush();
        f.close();*/

    }

    /**
     * 作废,清零
     *
     * @param request
     * @param studentstate
     * @param studentId
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/changeStudentState")
    public Student changeStudentState(HttpServletRequest request, String studentstate, String studentId, String remark) {
        Student student = new Student();
        student.setFid(studentId);
        student.setFdescriptionL1(remark);
        if (studentstate.equals("14")) {
            student.setCfstudentstate(Long.valueOf(studentstate));
            studentService.updateStudent(student);
            student.setCfdesc("作废成功");
            return student;
        } else if (studentstate.equals("1")) {
            student.setCfsnql((long) 1);
            studentService.updateStudent(student);
            student.setCfdesc("三年清零成功");
            return student;
        } else if (studentstate.equals("0")) {
            student.setCfwcql((long) 1);
            studentService.updateStudent(student);
            student.setCfdesc("五次清零成功");
            return student;
        } else {
        }
        return null;
    }


    /**
     * 保存学生
     *
     * @param request
     * @param student
     * @return
     * @throws Exception
     * @createTime 2017-11-7 2:20 pm
     * @author zyq.
     */
    @RequestMapping(value = "/saveStudent")
    public String saveStudent(HttpServletRequest request, Student student) throws Exception {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        String path = (String) request.getSession().getAttribute("path");
        StudentPhoto studentPhoto = new StudentPhoto();
        //利用UUID生成fid
        student.setFid(GetFid.UUIDIntoFid());
        //控制单元id
        student.setFcontrolunitid(user.getFcompanyid());
        //创建人id
        student.setFcreatorid(user.getFid());
        //公司id
        student.setCfficompanyid(user.getFcompanyid());
        //获取当前时间
        String createTime = TimeFormat.timeFormat(new Date());
        student.setFcreatetime(createTime);
        MultipartHttpServletRequest request2 = (MultipartHttpServletRequest) request;
        String file = request2.getParameter("file");
        BASE64Decoder decoder = new BASE64Decoder();
        // Base64解码
        byte[] image = decoder.decodeBuffer(file);
        for (int i = 0; i < image.length; ++i) {
            if (image[i] < 0) {// 调整异常数据
                image[i] += 256;
            }
        }
        /*String fnumber = customLogService.selectFunmber("CT_DS_STUDENT",user.getFcompanyid());*/
        String fnumber = customLogService.selectFunmber("ct_ds_student","X",GetUserSession.getUserSession(request));
        student.setFnumber(fnumber);
        student.setCfwcql((long) 0);
        student.setCfwcql((long) 0);
        studentPhoto.setFid(GetFid.UUIDIntoFid());
        studentPhoto.setFstudentid(student.getFid());
        studentPhoto.setFimagedata(image);
        Student student1 = studentService.queryStudentByIdCardNo((String) student.getCfidcardno(),user.getFcompanyid());
        if (student1 == null) {
            studentPhotoService.insertPhoto(studentPhoto);                                    //插入图片
            studentService.insertStudent(student);
            customLogService.addLog("学员入学登记", "办理了学员:"+student.getFnumber()+""+student.getFnameL2()+"入学登记", student.getFid(), user.getFid());
            request.setAttribute("type", 1);
        } else {
            if (student.getCfslzjcxdh().equals("C1")) {
                if (student1.getCfstudentstate() == 10 || student1.getCfstudentstate() == 11 || student1.getCfstudentstate() == 13 || student1.getCfstudentstate() == 14 || ((student1.getCfsnql() == null) ? 0 : student1.getCfsnql()) == 1 || ((student1.getCfwcql() == null) ? 0 : student1.getCfwcql()) == 1) {
                    studentPhotoService.insertPhoto(studentPhoto);                                    //插入图片
                    studentService.insertStudent(student);
                    customLogService.addLog("学员入学登记", "办理了学员:"+student.getFnumber()+""+student.getFnameL2()+"入学登记", student.getFid(), user.getFid());
                    request.setAttribute("type", 1);
                } else {
                    request.setAttribute("message", "0");
                }
            } else {
                if (student1.getCfstudentstate() == 10 || student1.getCfstudentstate() == 11 || student1.getCfstudentstate() == 13 || student1.getCfstudentstate() == 14 || ((student1.getCfsnql() == null) ? 0 : student1.getCfsnql()) == 1 || ((student1.getCfwcql() == null) ? 0 : student1.getCfwcql()) == 1) {
                    studentPhotoService.insertPhoto(studentPhoto);                                    //插入图片
                    studentService.insertStudent(student);
                    customLogService.addLog("学员入学登记", "办理了学员:"+student.getFnumber()+""+student.getFnameL2()+"入学登记", student.getFid(), user.getFid());
                    request.setAttribute("type", 1);
                } else {
                    request.setAttribute("message", "1");
                }
                /*studentPhotoService.insertPhoto(studentPhoto);                                    //插入图片
                studentService.insertStudent(student);
                customLogService.addLog("学员入学登记", createTime + "办理了入学登记", student.getFid(), user.getFid());*/
            }
        }
        return "forward:addStudent";
    }


    /**
     * 删除学员信息(真删除)
     *
     * @param request
     * @param fid     学员FID
     * @return
     * @throws Exception
     * @createTime 2017-11-9 4:20 pm
     * @author zyq.
     */
    @RequestMapping(value = "/deleteStudentByFid")
    public String deleteStudentByFid(HttpServletRequest request, String fid) throws Exception {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        String[] fids = fid.split(",");
        for (int i = 0; i < fids.length; i++) {
            Student student = new Student();
            List<Student> list = studentService.selectStudentByFid(fids[i]);
            if (!list.isEmpty())
                student = studentService.selectStudentByFid(fids[i]).get(0);
            customLogService.addLog("学员入学登记", "删除了"+student.getFnumber()+"" + student.getFnameL2() + "学员", student.getFid(), user.getFid());
        }
        studentService.deteleStudentByFid(fids);
        studentPhotoService.deleteByFstudentId(fids);
        selStudent(request);
        return "backHome/student/studentList";
    }

    /**
     * 简称是否可以报名
     *
     * @param request
     * @param fid
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/checkStudentIsStart")
    public Student checkStudentIsStart(HttpServletRequest request, String idno, String carType, String fid) {
        Student student1 = studentService.queryStudentByIdCardNo(idno,GetUserSession.getUserSession(request));
        if (student1 == null) {
            return null;
        } else {
            if (!(student1.getFid().equals(fid))) {
                if (carType.equals("C1")) {
                    if (student1.getCfstudentstate() == 10 || student1.getCfstudentstate() == 11 || student1.getCfstudentstate() == 13 || student1.getCfstudentstate() == 14 || ((student1.getCfsnql() == null) ? 0 : student1.getCfsnql()) == 1 || ((student1.getCfwcql() == null) ? 0 : student1.getCfwcql()) == 1) {
                    } else {
                        student1.setCfdesc("该学员已报名C1驾照");
                        return student1;
                    }
                } else {
                    if (student1.getCfstudentstate() == 10 || student1.getCfstudentstate() == 11 || student1.getCfstudentstate() == 13 || student1.getCfstudentstate() == 14 || ((student1.getCfsnql() == null) ? 0 : student1.getCfsnql()) == 1 || ((student1.getCfwcql() == null) ? 0 : student1.getCfwcql()) == 1) {

                    } else {
                        student1.setCfdesc("该学员正在学习C1驾照");
                        return student1;
                    }
                }
            }
        }
        return null;
    }


    /**
     * 更新学员信息
     *
     * @param request
     * @param student 学员
     * @return
     * @throws Exception
     * @createTime 2017-11-9 2:20 pm
     * @author zyq.
     */
    @RequestMapping(value = "/updateStudent")
    public String updateStudent(HttpServletRequest request, Student student, Long oldState,String oldTjrID,String oldTjrName) throws Exception {
        String createTime = TimeFormat.timeFormat(new Date());
        User user = (User) request.getSession().getAttribute("user");
        MultipartHttpServletRequest request2 = (MultipartHttpServletRequest) request;
        MultipartFile file = request2.getFile("file");
        if (file.isEmpty()) {
        } else {
            StudentPhoto studentPhoto = new StudentPhoto();
            studentPhoto.setFstudentid(student.getFid());
            studentPhoto.setFimagedata(file.getBytes());
            studentPhotoService.update(studentPhoto);
        }
        student.setFlastupdatetime(createTime);
        student.setFlastupdateuserid(user.getFid());
        List<Student> student1= studentService.selectStudentByFid(student.getFid());
        String oldrxsj=  student1.get(0).getCfrxsj();
        studentService.updateStudent(student);
        selStudent(request);
        request.setAttribute("type", 2);

        customLogService.addLog("学员入学登记", "修改了学员:"+student.getFnumber()+""+student.getFnameL2()+"入学登记", student.getFid(), user.getFid());

        Long state ;//当前状态

        //这里判断是否直接修改了学员的状态，如果修改了状态，添加一条日志
        if(null != (state = student.getCfstudentstate()) &&
               // null != oldState &&
                state != oldState) //当传入的学员状态不为空且与原本学员状态不一致时
        {
            List<Student> stateList = EnumListFactory.createEnumList();

            String nowState = (String)stateList.get(state.intValue()).getFnameL2();//当前状态

            String oldState2 = (String)stateList.get(oldState.intValue()).getFnameL2();//原本状态

            customLogService.addLog("学员入学登记", "修改了学员:"+student.getFnumber()+""+student.getFnameL2()+" 状态 "+oldState2+" ----> "+ nowState, student.getFid(), user.getFid());
        }
        //这里判断是否直接修改了学员的推荐人，如果修改了推荐人，添加一条日志
        if(StringUtils.isNotEmpty(oldTjrID) &&
                // null != oldState &&
                oldTjrID != student.getCftjrid()) //当传入的原推荐人id与当前推荐人id不符时加一条日志
        {
            customLogService.addLog("学员入学登记", "修改了学员:"+student.getFnumber()+""+student.getFnameL2()+" 推荐人 "+oldTjrName+" ----> "+ student.getCftjrName(), student.getFid(), user.getFid());
        }
        if(student.getCfrxsj()!=null){
            customLogService.addLog("学员入学登记", "修改了学员的入学时间:"+student.getFnumber()+""+student.getFnameL2()+"入学时间 "+oldrxsj+" ----> "+ student.getCfrxsj(), student.getFid(), user.getFid());

        }
        return "backHome/student/studentList";
    }

    /**
     * @Author lcx
     * @Date 2017/11/10 0010 下午 5:08
     * 通过ajax传入参数fid查询学生姓名
     */
    @RequestMapping(value = "/selStudentByFid")
    @ResponseBody
    public Object selStudentByFid(@RequestParam(value = "id", required = false) String id) throws Exception {
        List<Student> studentsNameList = new ArrayList<Student>();
        String[] ids = id.split(",");
        for (int i = 0; i < ids.length; i++) {
            List<Student> student = studentService.selectStudentByFid(ids[i]);
            if (student.size() > 1) {
                student.get(0).setCfckbk("2");
            }
            if(student.get(0).getCfreceived()==null){
                student.get(0).setCfreceived(BigDecimal.valueOf(0));
            }
            studentsNameList.add(student.get(0));
        }
        Map<String, List<Student>> studentsList = new HashMap<>();
        studentsList.put("studentsNameList", studentsNameList);
        return studentsList;
    }


    /**
     * 根据条件查询
     *
     * @param student
     * @param beginTime
     * @param endTime
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/selectStudentByCon")
    public String selectStudentByCon(Student student, String beginTime, String endTime, HttpServletRequest request) throws Exception {
        HttpSession session = request.getSession();
        User user = (User) session.getAttribute("user");                                   //获取已经登录用户数据
        Department department = departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
        PageBean<Student> pBean = getPageBean1(request, student, beginTime, endTime, department.getFid(),false);
        request.setAttribute("student1", student);
        request.setAttribute("sList", pBean.getBeanList());
        request.setAttribute("beginTime", beginTime);
        request.setAttribute("endTime", endTime);
        request.setAttribute("pb", pBean);
        //获取驾校对应部门
        List<Department> departments=departmentService.queryAdminByController(user.getFcompanyid());
        //部门信息
        request.setAttribute("DepartmentList",departments);
        //		rhc  信息总条数
        String messageAmount="共有"+pBean.getTr()+"条信息";
        request.setAttribute("messageAmount",messageAmount);
        return "backHome/student/studentList";
    }

    /**
     * 学生入学登记字段筛选
     * @param p
     * @param request
     * @return
     */
    @RequestMapping("/studentOutList")
    @ResponseBody
    public Object studentOutList(@RequestBody(required=true) Map<String,Object> p, HttpServletRequest request){
        ArrayList<String> arr= (ArrayList<String>) p.get("outList");
        request.getSession().setAttribute("studentOutList",arr);
        return arr;
    }






    /**
     * 导出学员登记excel文档
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/outStudentExcel")
    public void outStudentExcel(HttpServletResponse response, HttpServletRequest request,
                                    Student student, String beginTime, String endTime) throws Exception {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        List<Object> data = new ArrayList<>();
        //查询数据
        PageBean<Student> pBean =  getPageBean1(request,student,beginTime,endTime,user.getFcompanyid(),true);
        for (Student student1 : pBean.getBeanList()) {
            data.add(student1.getFnameL2());//单据编号
            data.add(student1.getFnumber());//业务日期
            data.add(student1.getCfidcardno());
            data.add(student1.getCfrxsj());
            data.add(student1.getCfsjhm());//制单人名称
            data.add(student1.getCflxdh());//学员
            data.add(student1.getCfbirthdays());//学员编码
            data.add(student1.getFsimplename());//身份证号
            data.add(student1.getFdescriptionL1());
            data.add(student1.getFcreatetime());
            data.add(student1.getFcreateName());
            data.add(student1.getFcreateName());
            data.add(student1.getFlastupdatetime());
            data.add(student1.getFlastupdateName());
            data.add(student1.getFlastupdateName());
            data.add(student1.getCfficompanyName());
            data.add("中国");
            data.add(student1.getCfnation());
            data.add(student1.getCfaddress());
            data.add(student1.getCflinkaddress());
            data.add(student1.getCfzzzh());
            data.add(student1.getCfyzbm());
            data.add(student1.getCfemail());
            if(student1.getCfsllx() == 1)
                data.add("初次申领");
            else data.add("增加准驾车型");
            data.add(student1.getCfxzjcxdh());
            data.add(student1.getCfslzjcxdh());
            data.add(student1.getCftjrName());
            data.add(student1.getCfclazzName());
            data.add(student1.getCfamount());
            if(student1.getCflocal() == 1)
                data.add("本地");
            else if(student1.getCflocal() == 2)
                data.add("异地");
            else data.add("");
            data.add(student1.getCftrainTypeNAME());
            data.add(student1.getCfdesc());
            if (student1.getCfsex() == null) {
                data.add("");//性别
            } else if (student1.getCfsex() == 2) {
                data.add("女");//性别
            } else {
                data.add("男");//性别
            }
            if (student1.getCftimecard() == null) {
                data.add("");//性别
            } else if (student1.getCftimecard() == 1) {
                data.add("已发放");//性别
            } else {
                data.add("未发放");//性别
            }
            data.add(stateToString.studentState(student1.getCfstudentstate()));
            data.add(student1.getCfamount());
            data.add(student1.getCfreceived());
            data.add(student1.getCffavorable());
            data.add(student1.getCfarrearage());
            data.add(student1.getCfkmykss());
            data.add(student1.getCfkmekss());
            data.add(student1.getCfkmskss());
            data.add(student1.getCfdepartment());
            data.add(student1.getCfkmyherq());
            data.add(student1.getCfkmehgrq());
            data.add(student1.getCfkmshgrq());
            data.add(student1.getCfzkzmbh());
            data.add(student1.getCfdazt());
            data.add(student1.getCfcfwz());
            data.add(student1.getCfkmybjgsj());
            data.add(student1.getCfkmebjgsj());
            data.add(student1.getCfkmsbjgsj());
            if("1".equals(student1.getCfbxwx()))
                data.add("本校本市");
            else if("2".equals(student1.getCfbxwx()))
                data.add("本市外校");
            else if("3".equals(student1.getCfbxwx()))
                data.add("地市考试");
            else if("4".equals(student1.getCfbxwx()))
                data.add("社会考生");
            else data.add("");
            data.add(student1.getCfjsybh());
            data.add(student1.getCfkmetlrq());
            data.add(student1.getCfbimudf0060());
            data.add(student1.getCfbimudf0061());
            data.add(student1.getCfkmstlrq());
            data.add(student1.getCfkmstlcs());
            data.add(student1.getCfkmstlyy());
            data.add(student1.getCfshortpinyin());
            data.add(student1.getCfpinyin());
            if(student1.getCfjnbjj() == null || student1.getCfjnbjj() != 1  )
                data.add("否");
            else
                data.add("是");
            if((student1.getCfdajj() == null) || student1.getCfdajj() != 1 )
                data.add("否");
            else
                data.add("是");
            data.add(student1.getCfkmelcsjName());
            data.add(student1.getCfkmslcsjName());
            data.add(student1.getCfmnkcs());
            data.add(student1.getCfkmyqkcs());
            data.add(student1.getCfmnkfs());
            data.add(student1.getCfmnksj());
            data.add(student1.getCfkmeqkcs());
            data.add(student1.getCfkmeksqksj());
            data.add(student1.getCfkmyksqksj());
            data.add(student1.getCfkmsqkcs());
            data.add(student1.getCfkmsksqksj());
            data.add(student1.getCfyhj());
            data.add(student1.getCfcfwz());
            if(student1.getCfsnql() == null || student1.getCfsnql() != 1 )
                data.add("否");
            else
                data.add("是");
            if(student1.getCfwcql() == null || student1.getCfwcql() != 1  )
                data.add("否");
            else
                data.add("是");
            data.add(student1.getCftrainTypeNAME());
            data.add(student1.getCfexambespeakremark());
        }
        //头部数据
        String head[] = {"学员名称", "编码", "身份证号码","入学日期", "手机","联系电话","出生日期", "简称","描述","创建时间",
                "创建者", "创建者用户名", "最后修改时间", "最后修改者", "修改者用户名", "财务组织", "国籍", "民族", "家庭地址", "联系地址",
                "暂住证号", "邮政编码", "电子邮箱", "申领类型", "现准驾车型号", "申请现准驾车型号", "推荐人", "班级", "班级金额", "本地/异地",
                "培训类型", "备注", "性别", "学员状态", "培训计时卡", "应收", "已收", "优惠", "欠费", "科目一考试次数",
                "科目二考试次数", "科目三考试次数", "部门", "科目一合格日期", "科目二合格日期", "科目三合格日期", "准考证明编号", "手续状态", "存放位置", "科目一不及格时间",
                "科目二不及格时间", "科目三不及格时间", "本校/外校", "结业证编号", "科目二退练日期", "科目二退练次数", "科目二退练原因", "科目三退练日期", "科目三退练次数", "科目三退练原因",
                "拼音首字母", "拼音", "技能表已交接", "档案已交接", "科目二练车班次", "科目三练车班次", "模拟考次数", "科目一缺考次数", "模拟考分数", "模拟考时间",
                "科目二缺考次数", "科目二缺考时间", "科目一缺考时间", "科目三缺考次数", "科目三缺考时间", "优惠券", "存放位置", "三年清零", "五次清零", "预约考试备注",
                "预约练车备注"};
        //长度
        Integer length[] = {4500, 6500, 6500, 3000, 3000, 3000, 3200, 3200, 3000, 4000,
                            4500, 3000, 4500, 3000, 3000, 5000, 3200, 3200, 6000, 3000,
                            4500, 3500, 3500, 3000, 3000, 4000, 3200, 3200, 3000, 3000,
                            4500, 3500, 3500, 3000, 3000, 3000, 3200, 3200, 3000, 3000,
                            4500, 3500, 3500, 4000, 4000, 3000, 3200, 3200, 3000, 4500,
                            4500, 4500, 3500, 3000, 4000, 4000, 4200, 4200, 4000, 6000,
                            4500, 3500, 3500, 3000, 4000, 4000, 3200, 3500, 3000, 3000,
                            4500, 4500, 4500, 4000, 4000, 4000, 3200, 3200, 3000, 4000,
                            4000};
        try {
            //导出Excel
            CommonExport.importExcel(response, "学员入学登记" + TimeFormat.getNowTime2() + ".xls", "学员入学登记", data, head, length);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * @Author
     * @Date 2017/11/10 0010 下午 5:08
     * 通过ajax传入参数fid查询计时收费学生姓名
     */
    @RequestMapping(value = "/selStudentByFidTiming")
    @ResponseBody
    public Object selStudentByFidTiming(@RequestParam(value = "id", required = false) String id) throws Exception {
        List<Student> studentsNameList = new ArrayList<Student>();
        String[] ids = id.split(",");
        for (int i = 0; i < ids.length; i++) {
            List<Student> student = studentService.selectStudentByFidTiming(ids[i]);

            //获取科目对应的系统参数数据与计时时长乘积赋值给应收金额
            for (int j = 0; j < student.size(); j++) {
                int subjectNumber = student.get(j).getCfsubject();//科目数字
                String timingBeginTime = student.get(j).getCfbegintime();//计时开始时间
                String timingEndTime = student.get(j).getCfendtime();//计时结束时间
                String paramName = null;//系统参数名称
                timingBeginTime = timingBeginTime.replace("-", "");
                timingBeginTime = timingBeginTime.replace(":", "");
                timingBeginTime = timingBeginTime.replace(" ", "");
                timingEndTime = timingEndTime.replace("-", "");
                timingEndTime = timingEndTime.replace(":", "");
                timingEndTime = timingEndTime.replace(" ", "");

                if ((timingBeginTime != null && !timingBeginTime.equals("")) &&
                        (timingEndTime != null && !timingEndTime.equals(""))
                        ) {
                    //训练时间
                    long from = simpleFormat.parse(timingBeginTime).getTime();
                    long to = simpleFormat.parse(timingEndTime).getTime();
                    int timingHour = (int) ((to - from) / (1000 * 60 * 60));

                    int timingMinute = Integer.parseInt(timingEndTime.substring(10, 12)) - Integer.parseInt(timingBeginTime.substring(10, 12));
                    if (timingMinute > 0) {
                        timingHour = timingHour + 1;
                    }
                    BigDecimal bigTimingHour = new BigDecimal(Integer.toString(timingHour));
                    if (subjectNumber == 2) {
                        paramName = "计时预约(科目二)/小时";
                        TrainCharge paramList = timingChargeService.selectSystemParamByName(paramName);
                        student.get(j).setCftimingmoney(bigTimingHour.multiply(paramList.getParamNumber()));
                    } else if (subjectNumber == 3) {
                        paramName = "计时预约(科目三)/小时";
                        TrainCharge paramList = timingChargeService.selectSystemParamByName(paramName);
                        student.get(j).setCftimingmoney(bigTimingHour.multiply(paramList.getParamNumber()));
                    }

                }

            }

            if (student.size() > 1) {
                student.get(0).setCfckbk("2");
            }
            studentsNameList.add(student.get(0));
        }
        Map<String, List<Student>> studentsList = new HashMap<>();
        studentsList.put("studentsNameList", studentsNameList);
        return studentsList;
    }


    @SuppressWarnings("unchecked")
    /**
     * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
     * @return 返回PageBean实体类
     * @throws Exception
     */
    public PageBean<Student> getPageBean(HttpServletRequest request, String fid, String beginTime, String endTime) throws Exception {

        int offset;//从第几条数据开始加载
        if (request.getParameter("pager.offset") == null) {
            offset = 0;
        } else {
            offset = Integer.parseInt(request.getParameter("pager.offset"));
        }
        String psql = "select to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                + "to_char(t.CFRXSJ,'yyyy-MM-dd ')  cfrxsj,"
                + "to_char(t.CFKMYHERQ,'YYYY-MM-DD HH24:MI:SS')  cfkmyherq,"
                + "to_char(t.CFBIRTHDAY,'YYYY-MM-DD') cfbirthdays,"
                + "to_char(t.CFKMEHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmehgrq,"
                + "to_char(t.CFKMSHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmshgrq,"
                + "to_char(t.CFKMYBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmybjgsj,"
                + "to_char(t.CFKMSBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmsbjgsj,"
                + "to_char(t.CFKMEBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmebjgsj,"
                + "to_char(t.CFKMETLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmetlrq,"
                + "to_char(t.CFKMSTLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmstlrq,"
                + "to_char(t.CFMNKHGSJ,'YYYY-MM-DD HH24:MI:SS') cfmnkhgsj,"
                + "to_char(t.CFMNKBJGRQ,'YYYY-MM-DD HH24:MI:SS') cfmnkbjgrq,"
                + "to_char(t.CFKMEKSQKSJ,'YYYY-MM-DD HH24:MI:SS') cfkmeksqksj,"
                + "to_char(t.CFMNKSJ,'YYYY-MM-DD HH24:MI:SS') cfmnksj ,"
                + "t.FID, t.FNUMBER, t.FSIMPLENAME, t.FCREATORID, t.FLASTUPDATEUSERID, "
                + "t.FCONTROLUNITID, t.CFFICOMPANYID, t.CFNATIONALITYID, t.CFIDCARDNO, t.CFNATION, "
                + "t.CFADDRESS, t.CFLINKADDRESS, t.CFZZZH, t.CFSJHM, t.CFLXDH, t.CFYZBM, t.CFEMAIL, t.CFSLLX, t.CFXZJCXDH, "
                + "t.CFSLZJCXDH, t.CFTJRID, t.CFCLAZZID, t.CFAMOUNT, t.CFLOCAL, t.CFTRAINTYPEID, t.CFREMARK, "
                + "t.CFSEX, t.CFSTUDENTSTATE, t.CFTIMECARD, t.CFRECEIVED, t.CFARREARAGE, t.CFFAVORABLE, t.CFKMYKSS, "
                + " t.CFKMEKSS, t.CFKMSKSS, t.CFDEPARTMENT, t.CFZKZMBH, t.CFDAZT, "
                + "t.CFCFWZ, t.CFDESC, t.CFBXWX, t.CFJSYBH, t.CFBIMUDF0060, "
                + " t.CFBIMUDF0061,  t.CFKMSTLCS, t.CFKMSTLYY, t.CFSHORTPINYIN, t.CFPINYIN, t.FNAME_L1, "
                + "t.FNAME_L2, t.FDESCRIPTION_L1, t.FDESCRIPTION_L2, t.FDESCRIPTION_L3, t.CFJNBJJ, t.CFDAJJ, "
                + " t.CFKMELCSJID, t.CFKMSLCSJID,  t.CFMNKCS, t.CFKMEQKCS, "
                + " t.CFMNKFS, t.CFKMYQKCS, t.CFKMSQKCS,  t.CFYHJ, t.CFSNQL, "
                + " t.CFWCQL, t.CFTRAINBESPEAKREMARK, t.CFEXAMBESPEAKREMARK, t.LNG, t.LAT, t.DISTRICT, t.CITY, t.PROVINCE,u.name as FCREATENAME,u1.name as FLASTUPDATENAME,u2.FNAME_L2 AS CFTJRNAME,C.FNAME_L2 AS CFCLAZZNAME,Y.FNAME_L2 AS CFTRAINTYPENAME "
                + " ,E1.FNAME_L2 AS CFKMELCSJNAME,E2.FNAME_L2 AS CFKMSLCSJNAME ,A.FNAME_L2 AS cfficompanyName"
                + "  FROM CT_DS_STUDENT T LEFT JOIN T_E_USER U ON T.FCREATORID=U.FID LEFT JOIN T_E_USER U1 ON T.FLASTUPDATEUSERID=U1.FID LEFT JOIN CT_DS_PERSON  u2 on t.cftjrid=u2.fid left join CT_DS_CLAZZ C ON T.cfclazzid=C.FID left JOIN CT_DS_ADMIN A ON T.cfficompanyid=A.FID"
                + " LEFT JOIN  CT_DS_TRAINTYPE Y ON T.CFTRAINTYPEID=Y.FID LEFT JOIN CT_DS_EXERCISECARPLAN E1 ON E1.FID=T.CFKMELCSJID LEFT JOIN CT_DS_EXERCISECARPLAN E2 ON E2.FID=T.CFKMSLCSJID "
                + " where t.CFFICOMPANYID= '" + fid + "'";
        if (beginTime != null && endTime != null) {
            psql += " and to_char(t.CFRXSJ,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
        }
        psql +=" order by FCREATETIME desc";
        int psize = 15; //每页显示数
        int pindex = offset / psize;//+1  页码从0开始
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = studentService.selectPage(pindex, psql, psize);
        PageBean<Student> pb = new PageBean<>();
        pb.setPc(pindex); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        pb.setBeanList((List<Student>) paramMap.get("v_cur")); //每页数据集
        return pb;

    }


    /**
     * @Author lcx
     * @Date 2017/11/16 0016 上午 8:47
     * 档案管理查询学生信息
     */
    @SuppressWarnings("unchecked")
    @ResponseBody
    @RequestMapping(value = "/getPage1")
    public Map getPageBean2(HttpServletRequest request, @RequestParam(defaultValue = "", value = "name") String name, @RequestParam(defaultValue = "") String cflxdh, @RequestParam(defaultValue = "0") String pindex, String beginTime, String endTime) throws Exception {

        String psql = "select to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                + "to_char(t.CFRXSJ,'yyyy-MM-dd ')  cfrxsj,"
                + "to_char(t.CFKMYHERQ,'YYYY-MM-DD HH24:MI:SS')  cfkmyherq,"
                + "to_char(t.CFBIRTHDAY,'YYYY-MM-DD') cfbirthdays,"
                + "to_char(t.CFKMEHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmehgrq,"
                + "to_char(t.CFKMSHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmshgrq,"
                + "to_char(t.CFKMYBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmybjgsj,"
                + "to_char(t.CFKMSBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmsbjgsj,"
                + "to_char(t.CFKMEBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmebjgsj,"
                + "to_char(t.CFKMETLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmetlrq,"
                + "to_char(t.CFKMSTLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmstlrq,"
                + "to_char(t.CFMNKHGSJ,'YYYY-MM-DD HH24:MI:SS') cfmnkhgsj,"
                + "to_char(t.CFMNKBJGRQ,'YYYY-MM-DD HH24:MI:SS') cfmnkbjgrq,"
                + "to_char(t.CFKMEKSQKSJ,'YYYY-MM-DD HH24:MI:SS') cfkmeksqksj,"
                + "to_char(t.CFMNKSJ,'YYYY-MM-DD HH24:MI:SS') cfmnksj ,"
                + "t.FID, t.FNUMBER, t.FSIMPLENAME, t.FCREATORID, t.FLASTUPDATEUSERID, "
                + "t.FCONTROLUNITID, t.CFFICOMPANYID, t.CFNATIONALITYID, t.CFIDCARDNO, t.CFNATION, "
                + "t.CFADDRESS, t.CFLINKADDRESS, t.CFZZZH, t.CFSJHM, t.CFLXDH, t.CFYZBM, t.CFEMAIL, t.CFSLLX, t.CFXZJCXDH, "
                + "t.CFSLZJCXDH, t.CFTJRID, t.CFCLAZZID, t.CFAMOUNT, t.CFLOCAL, t.CFTRAINTYPEID, t.CFREMARK, "
                + "t.CFSEX, t.CFSTUDENTSTATE, t.CFTIMECARD, t.CFRECEIVED, t.CFARREARAGE, t.CFFAVORABLE, t.CFKMYKSS, "
                + " t.CFKMEKSS, t.CFKMSKSS, t.CFDEPARTMENT, t.CFZKZMBH, t.CFDAZT, "
                + "t.CFCFWZ, t.CFDESC, t.CFBXWX, t.CFJSYBH, t.CFBIMUDF0060, "
                + " t.CFBIMUDF0061,  t.CFKMSTLCS, t.CFKMSTLYY, t.CFSHORTPINYIN, t.CFPINYIN, t.FNAME_L1, "
                + "t.FNAME_L2, t.FDESCRIPTION_L1, t.FDESCRIPTION_L2, t.FDESCRIPTION_L3, t.CFJNBJJ, t.CFDAJJ, "
                + " t.CFKMELCSJID, t.CFKMSLCSJID,  t.CFMNKCS, t.CFKMEQKCS, "
                + " t.CFMNKFS, t.CFKMYQKCS, t.CFKMSQKCS,  t.CFYHJ, t.CFSNQL, "
                + " t.CFWCQL, t.CFTRAINBESPEAKREMARK, t.CFEXAMBESPEAKREMARK, t.LNG, t.LAT, t.DISTRICT, t.CITY, t.PROVINCE,u.name as FCREATENAME,u1.name as FLASTUPDATENAME,u2.FNAME_L2 AS CFTJRNAME,C.FNAME_L2 AS CFCLAZZNAME,Y.FNAME_L2 AS CFTRAINTYPENAME"
                + " ,E1.FNAME_L2 AS CFKMELCSJNAME,E2.FNAME_L2 AS CFKMSLCSJNAME ,A.FNAME_L2 AS cfficompanyName "
                + "  FROM CT_DS_STUDENT T LEFT JOIN T_E_USER U ON T.FCREATORID=U.FID LEFT JOIN T_E_USER U1 ON T.FLASTUPDATEUSERID=U1.FID LEFT JOIN CT_DS_PERSON  u2 on t.cftjrid=u2.fid left join CT_DS_CLAZZ C ON T.cfclazzid=C.FID"
                + " LEFT JOIN  CT_DS_TRAINTYPE Y ON T.CFTRAINTYPEID=Y.FID LEFT JOIN CT_DS_EXERCISECARPLAN E1 ON E1.FID=T.CFKMELCSJID LEFT JOIN CT_DS_EXERCISECARPLAN E2 ON E2.FID=T.CFKMSLCSJID LEFT JOIN CT_DS_ADMIN A ON T.CFFICOMPANYID=A.FID"
                + " WHERE  t.cfstudentstate != 10 AND t.cfstudentstate != 11 AND t.cfstudentstate != 13 AND t.cfstudentstate != 14  and t.FCONTROLUNITID= '" + ((User) request.getSession().getAttribute("user")).getFcompanyid() + "'";
        if (!"".equals(name)) {
            psql += "AND t.FNAME_L2 LIKE '%" + name + "%' ";
        }
        if (!"".equals(cflxdh)) {
            psql += "AND t.cflxdh LIKE '%" + cflxdh + "%' ";
        }
        if (!"".equals(beginTime) && !"".equals(endTime)) {
            psql +=" and      (DECODE(sign( to_char(t.CFKMSHGRQ, 'yyyyMMdd') - to_char(t.CFKMEHGRQ, 'yyyyMMdd')), '-1', t.CFKMEHGRQ, '0', " +
                    "               t.CFKMSHGRQ, '1', t.CFKMSHGRQ) BETWEEN to_date('" + beginTime + "','YYYY/MM/DD') AND to_date('" + endTime + "','YYYY/MM/DD') + 1) " +
                    "order by (DECODE(sign( to_char(t.CFKMSHGRQ, 'yyyyMMdd') - to_char(t.CFKMEHGRQ, 'yyyyMMdd') ), '-1', t.CFKMEHGRQ, '0', " +
                    "                 t.CFKMSHGRQ, '1', t.CFKMSHGRQ)) desc";
        }
        int psize = 50; //每页显示数
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = studentService.selectPage(Integer.valueOf(pindex), psql, psize);
        PageBean<Student> pb = new PageBean<>();
        pb.setPc(Integer.valueOf(pindex)); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        Map stu = new HashMap();
        stu.put("sList", (List<Student>) paramMap.get("v_cur"));

        stu.put("pb", (pb.getTr() / pb.getPs()));

        return stu; //每页数据集;

    }

    /*培訓收費的 查詢學生 被其他人乱用了  修改的时候注意 现在培训收费 和其他学生弹出窗的内容是一致的  修改此方法 注意修改 各个页面中的调用位置
     * 如 Map map = studentController.getPageAsTrainCharge(request, "", "", "0", beginTime, endTime);//调用学员方法
      * */
    @SuppressWarnings("unchecked")
    @ResponseBody
    @RequestMapping(value = "/getPageAsTrainCharge")
    public Map getPageAsTrainCharge(HttpServletRequest request, @RequestParam(defaultValue = "", value = "name") String name,
                                    @RequestParam(defaultValue = "") String cflxdh,
                                    @RequestParam(defaultValue = "0") String pindex,
                                    @RequestParam(defaultValue = "1") String specifiedCost,
                                    @RequestParam(defaultValue = "") String classSize,
                                    @RequestParam(defaultValue = "") String referrer,
                                    String beginTime, String endTime) throws Exception {

        if (request.getSession().getAttribute(Constant.CompanyName_session).equals("南昌机动车驾驶员培训中心")){
                    beginTime="";
        }

        String psql = "select to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                + "to_char(t.CFRXSJ,'yyyy-MM-dd ')  cfrxsj,"
                + "to_char(t.CFKMYHERQ,'YYYY-MM-DD HH24:MI:SS')  cfkmyherq,"
                + "to_char(t.CFBIRTHDAY,'YYYY-MM-DD') cfbirthdays,"
                + "to_char(t.CFKMEHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmehgrq,"
                + "to_char(t.CFKMSHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmshgrq,"
                + "to_char(t.CFKMYBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmybjgsj,"
                + "to_char(t.CFKMSBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmsbjgsj,"
                + "to_char(t.CFKMEBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmebjgsj,"
                + "to_char(t.CFKMETLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmetlrq,"
                + "to_char(t.CFKMSTLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmstlrq,"
                + "to_char(t.CFMNKHGSJ,'YYYY-MM-DD HH24:MI:SS') cfmnkhgsj,"
                + "to_char(t.CFMNKBJGRQ,'YYYY-MM-DD HH24:MI:SS') cfmnkbjgrq,"
                + "to_char(t.CFKMEKSQKSJ,'YYYY-MM-DD HH24:MI:SS') cfkmeksqksj,"
                + "to_char(t.CFMNKSJ,'YYYY-MM-DD HH24:MI:SS') cfmnksj ,"
                + "t.FID, t.FNUMBER, t.FSIMPLENAME, t.FCREATORID, t.FLASTUPDATEUSERID, "
                + "t.FCONTROLUNITID, t.CFFICOMPANYID, t.CFNATIONALITYID, t.CFIDCARDNO, t.CFNATION, "
                + "t.CFADDRESS, t.CFLINKADDRESS, t.CFZZZH, t.CFSJHM, t.CFLXDH, t.CFYZBM, t.CFEMAIL, t.CFSLLX, t.CFXZJCXDH, "
                + "t.CFSLZJCXDH, t.CFTJRID, t.CFCLAZZID, t.CFAMOUNT, t.CFLOCAL, t.CFTRAINTYPEID, t.CFREMARK, "
                + "t.CFSEX, t.CFSTUDENTSTATE, t.CFTIMECARD, t.CFRECEIVED, t.CFARREARAGE, t.CFFAVORABLE, t.CFKMYKSS, "
                + " t.CFKMEKSS, t.CFKMSKSS, t.CFDEPARTMENT, t.CFZKZMBH, t.CFDAZT, "
                + "t.CFCFWZ, t.CFDESC, t.CFBXWX, t.CFJSYBH, t.CFBIMUDF0060, "
                + " t.CFBIMUDF0061,  t.CFKMSTLCS, t.CFKMSTLYY, t.CFSHORTPINYIN, t.CFPINYIN, t.FNAME_L1, "
                + "t.FNAME_L2, t.FDESCRIPTION_L1, t.FDESCRIPTION_L2, t.FDESCRIPTION_L3, t.CFJNBJJ, t.CFDAJJ, "
                + " t.CFKMELCSJID, t.CFKMSLCSJID,  t.CFMNKCS, t.CFKMEQKCS, "
                + " t.CFMNKFS, t.CFKMYQKCS, t.CFKMSQKCS,  t.CFYHJ, t.CFSNQL, "
                + " t.CFWCQL, t.CFTRAINBESPEAKREMARK, t.CFEXAMBESPEAKREMARK, t.LNG, t.LAT, t.DISTRICT, t.CITY, t.PROVINCE,u.name as FCREATENAME,u1.name as FLASTUPDATENAME,u2.FNAME_L2 AS CFTJRNAME,C.FNAME_L2 AS CFCLAZZNAME,Y.FNAME_L2 AS CFTRAINTYPENAME"
                + " ,E1.FNAME_L2 AS CFKMELCSJNAME,E2.FNAME_L2 AS CFKMSLCSJNAME ,A.FNAME_L2 AS cfficompanyName "
                + "  FROM CT_DS_STUDENT T LEFT JOIN T_E_USER U ON T.FCREATORID=U.FID LEFT JOIN T_E_USER U1 ON T.FLASTUPDATEUSERID=U1.FID LEFT JOIN CT_DS_PERSON  u2 on t.cftjrid=u2.fid left join CT_DS_CLAZZ C ON T.cfclazzid=C.FID"
                + " LEFT JOIN  CT_DS_TRAINTYPE Y ON T.CFTRAINTYPEID=Y.FID LEFT JOIN CT_DS_EXERCISECARPLAN E1 ON E1.FID=T.CFKMELCSJID LEFT JOIN CT_DS_EXERCISECARPLAN E2 ON E2.FID=T.CFKMSLCSJID LEFT JOIN CT_DS_ADMIN A ON T.CFFICOMPANYID=A.FID"
                + " WHERE  t.cfstudentstate != 10 AND t.cfstudentstate != 11 AND t.cfstudentstate != 13 AND t.cfstudentstate != 14  and t.FCONTROLUNITID= '" + ((User) request.getSession().getAttribute("user")).getFcompanyid() + "'";
        if (!"".equals(name)) {
            psql += "AND t.FNAME_L2 LIKE '%" + name + "%' ";
        }
        if (!"".equals(cflxdh)) {
            psql += " AND t.CFSJHM LIKE '%" + cflxdh + "%' ";
        }
        if (!"".equals(beginTime) && !"".equals(endTime)) {
            psql += " AND (t.CFRXSJ BETWEEN to_date('" + beginTime + "','YYYY/MM/DD') AND to_date('" + endTime + "','YYYY/MM/DD'))";
        }
        if (!"".equals(specifiedCost)) {
            //欠费  1为欠费  0为不欠费
            if ("1".equals(specifiedCost)){
                psql += " AND t.CFARREARAGE > 0 ";

            }else if("6".equals(specifiedCost)) {
                   //6为退费状态因此不需要添加sql语句判断
            }else{
                psql += " AND t.CFARREARAGE = 0 ";

            }
        }
        if(!"".equals(classSize)){
            if("0".equals(classSize)){

                classSize="二部";
                psql+="AND C.FNAME_L2 not like'%"+classSize+"%'";
            }else if("1".equals(classSize)){
              classSize="二部";
                psql+="AND C.FNAME_L2 like'%"+classSize+"%'";

            }else{

            }
        }
        if(!"".equals(referrer)){
            psql+="AND u2.FNAME_L2 like'%"+referrer+"%'";
        }
        psql += "order by t.CFRXSJ desc";
        int psize = 50; //每页显示数
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = studentService.selectPage(Integer.valueOf(pindex), psql, psize);
        PageBean<Student> pb = new PageBean<>();
        pb.setPc(Integer.valueOf(pindex)); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        Map stu = new HashMap();
        stu.put("sList", (List<Student>) paramMap.get("v_cur"));

        stu.put("pb", (pb.getTr() / pb.getPs()));

        return stu; //每页数据集;

    }

    @SuppressWarnings("unchecked")
    /**
     * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
     * @return 返回PageBean实体类
     * @throws Exception
     */
    public PageBean<Student> getPageBean1(HttpServletRequest request, Student student, String beginTime, String endTime, String fid,boolean isExcle) throws Exception {

        int offset;//从第几条数据开始加载
        if (request.getParameter("pager.offset") == null) {
            offset = 0;
        } else {
            offset = Integer.parseInt(request.getParameter("pager.offset"));
        }
        String psql = "select to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                + "to_char(t.CFRXSJ,'yyyy-MM-dd ')  cfrxsj,"
                + "to_char(t.CFKMYHERQ,'YYYY-MM-DD HH24:MI:SS')  cfkmyherq,"
                + "to_char(t.CFBIRTHDAY,'YYYY-MM-DD') cfbirthdays,"
                + "to_char(t.CFKMEHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmehgrq,"
                + "to_char(t.CFKMSHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmshgrq,"
                + "to_char(t.CFKMYBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmybjgsj,"
                + "to_char(t.CFKMSBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmsbjgsj,"
                + "to_char(t.CFKMEBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmebjgsj,"
                + "to_char(t.CFKMETLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmetlrq,"
                + "to_char(t.CFKMSTLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmstlrq,"
                + "to_char(t.CFMNKHGSJ,'YYYY-MM-DD HH24:MI:SS') cfmnkhgsj,"
                + "to_char(t.CFMNKBJGRQ,'YYYY-MM-DD HH24:MI:SS') cfmnkbjgrq,"
                + "to_char(t.CFKMEKSQKSJ,'YYYY-MM-DD HH24:MI:SS') cfkmeksqksj,"
                + "to_char(t.CFMNKSJ,'YYYY-MM-DD HH24:MI:SS') cfmnksj ,"
                + "T.FID, T.FNUMBER, T.FSIMPLENAME, T.FCREATORID, T.FLASTUPDATEUSERID, "
                + "T.FCONTROLUNITID, T.CFFICOMPANYID, T.CFNATIONALITYID, T.CFIDCARDNO, T.CFNATION, "
                + "T.CFADDRESS, T.CFLINKADDRESS, T.CFZZZH, T.CFSJHM, T.CFLXDH, T.CFYZBM, T.CFEMAIL, T.CFSLLX, T.CFXZJCXDH, "
                + "T.CFSLZJCXDH, T.CFTJRID, T.CFCLAZZID, T.CFAMOUNT, T.CFLOCAL, T.CFTRAINTYPEID, T.CFREMARK, "
                + "T.CFSEX, T.CFSTUDENTSTATE, T.CFTIMECARD, T.CFRECEIVED, T.CFARREARAGE, T.CFFAVORABLE, T.CFKMYKSS, "
                + " T.CFKMEKSS, T.CFKMSKSS, T.CFDEPARTMENT, T.CFZKZMBH, T.CFDAZT, "
                + "T.CFCFWZ, T.CFDESC, T.CFBXWX, T.CFJSYBH, T.CFBIMUDF0060, "
                + " T.CFBIMUDF0061,  T.CFKMSTLCS, T.CFKMSTLYY, T.CFSHORTPINYIN, T.CFPINYIN, T.FNAME_L1, "
                + "T.FNAME_L2, T.FDESCRIPTION_L1, T.FDESCRIPTION_L2, T.FDESCRIPTION_L3, T.CFJNBJJ, T.CFDAJJ, "
                + " T.CFKMELCSJID, T.CFKMSLCSJID,  T.CFMNKCS, T.CFKMEQKCS, "
                + " T.CFMNKFS, T.CFKMYQKCS, T.CFKMSQKCS,  T.CFYHJ, T.CFSNQL, "
                + " T.CFWCQL, T.CFTRAINBESPEAKREMARK, T.CFEXAMBESPEAKREMARK, T.LNG, T.LAT, T.DISTRICT, T.CITY, T.PROVINCE,U.NAME AS FCREATENAME,U1.NAME AS FLASTUPDATENAME,U2.FNAME_L2 AS CFTJRNAME,C.FNAME_L2 AS CFCLAZZNAME,Y.FNAME_L2 AS CFTRAINTYPENAME "
                + " ,E1.FNAME_L2 AS CFKMELCSJNAME,E2.FNAME_L2 AS CFKMSLCSJNAME ,A.FNAME_L2 AS cfficompanyName "
                + "  FROM CT_DS_STUDENT T LEFT JOIN T_E_USER U ON T.FCREATORID=U.FID LEFT JOIN T_E_USER U1 ON T.FLASTUPDATEUSERID=U1.FID LEFT JOIN CT_DS_PERSON  U2 ON T.CFTJRID=U2.FID LEFT JOIN CT_DS_CLAZZ C ON T.CFCLAZZID=C.FID LEFT JOIN CT_DS_ADMIN A ON T.CFFICOMPANYID=A.FID"
                + " LEFT JOIN  CT_DS_TRAINTYPE Y ON T.CFTRAINTYPEID=Y.FID LEFT JOIN CT_DS_EXERCISECARPLAN E1 ON E1.FID=T.CFKMELCSJID LEFT JOIN CT_DS_EXERCISECARPLAN E2 ON E2.FID=T.CFKMSLCSJID "
                + " WHERE T.CFFICOMPANYID= '" + fid + "' and 1=1 ";
        if ("".equals(student.getCfstudentstate()) || student.getCfstudentstate() == 0) {

        } else {
            psql += " and  T.CFSTUDENTSTATE = " + student.getCfstudentstate();
        }
        if ("".equals(student.getCfsjhm()) || student.getCfsjhm() == null) {

        } else {
            psql += " and t.cfsjhm like '%" + student.getCfsjhm() + "%'";
        }
        if ("".equals(student.getCfidcardno()) || student.getCfidcardno() == null) {

        } else {
            psql += " and t.cfidcardno like '%" + student.getCfidcardno() + "%'";
        }
        if ("".equals(student.getFnameL2()) || student.getFnameL2() == null) {

        } else {
            psql += " and t.fname_l2 like '%" + student.getFnameL2() + "%'";
        }
        if ("".equals(student.getKmefcState()) || student.getKmefcState() == null) {

        } else {
            psql += " and t.CFKMELCSJID is not null";
        }
        if ("".equals(student.getKmsfcState()) || student.getKmsfcState() == null) {

        } else {
            psql += " and t.CFKMSLCSJID is not null";
        }
        if ( beginTime ==null || "".equals(beginTime) || endTime ==null || "".equals(endTime)) {
        } else {
            psql += " and to_char(t.CFRXSJ,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
        }
        psql +=" order by FCREATETIME desc";
        Map<String, Object> paramMapCount=getPageBeanCount(psql); //取记录总数
        int psize = 0;
        if(isExcle)
            if((int)paramMapCount.get("Pcount") == 0)
                psize = 1;
                else
                psize = (int)paramMapCount.get("Pcount");
        else
            psize=100; //每页显示数
        int pindex = 1;
             pindex = offset / psize;//+1  页码从0开始
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = studentService.selectPage(pindex, psql, psize);
        PageBean<Student> pb = new PageBean<>();
        pb.setPc(pindex); //页码
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        pb.setBeanList((List<Student>) paramMap.get("v_cur")); //每页数据集
        return pb;
    }

    /**
     * @Author
     * @Date 查询入学登记计时收费学员信息
     */
    @SuppressWarnings("unchecked")
    @ResponseBody
    @RequestMapping(value = "/getPage2")
    public Map getPageBean3(HttpServletRequest request, @RequestParam(defaultValue = "", value = "name") String name, @RequestParam(defaultValue = "") String cflxdh, @RequestParam(defaultValue = "0") String pindex, String beginTime, String endTime) throws Exception {

        String psql = "select to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                + "to_char(t.CFRXSJ,'yyyy-MM-dd ')  cfrxsj,"
                + "to_char(t.CFKMYHERQ,'YYYY-MM-DD HH24:MI:SS')  cfkmyherq,"
                + "to_char(t.CFBIRTHDAY,'YYYY-MM-DD') cfbirthdays,"
                + "to_char(t.CFKMEHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmehgrq,"
                + "to_char(t.CFKMSHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmshgrq,"
                + "to_char(t.CFKMYBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmybjgsj,"
                + "to_char(t.CFKMSBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmsbjgsj,"
                + "to_char(t.CFKMEBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmebjgsj,"
                + "to_char(t.CFKMETLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmetlrq,"
                + "to_char(t.CFKMSTLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmstlrq,"
                + "to_char(t.CFMNKHGSJ,'YYYY-MM-DD HH24:MI:SS') cfmnkhgsj,"
                + "to_char(t.CFMNKBJGRQ,'YYYY-MM-DD HH24:MI:SS') cfmnkbjgrq,"
                + "to_char(t.CFKMEKSQKSJ,'YYYY-MM-DD HH24:MI:SS') cfkmeksqksj,"
                + "to_char(t.CFMNKSJ,'YYYY-MM-DD HH24:MI:SS') cfmnksj ,"
                + "t.FID, t.FNUMBER, t.FSIMPLENAME, t.FCREATORID, t.FLASTUPDATEUSERID, "
                + "t.FCONTROLUNITID, t.CFFICOMPANYID, t.CFNATIONALITYID, t.CFIDCARDNO, t.CFNATION, "
                + "t.CFADDRESS, t.CFLINKADDRESS, t.CFZZZH, t.CFSJHM, t.CFLXDH, t.CFYZBM, t.CFEMAIL, t.CFSLLX, t.CFXZJCXDH, "
                + "t.CFSLZJCXDH, t.CFTJRID, t.CFCLAZZID, t.CFAMOUNT, t.CFLOCAL, t.CFTRAINTYPEID, t.CFREMARK, "
                + "t.CFSEX, t.CFSTUDENTSTATE, t.CFTIMECARD, t.CFRECEIVED, t.CFARREARAGE, t.CFFAVORABLE, t.CFKMYKSS, "
                + " t.CFKMEKSS, t.CFKMSKSS, t.CFDEPARTMENT, t.CFZKZMBH, t.CFDAZT, "
                + "t.CFCFWZ, t.CFDESC, t.CFBXWX, t.CFJSYBH, t.CFBIMUDF0060, "
                + " t.CFBIMUDF0061,  t.CFKMSTLCS, t.CFKMSTLYY, t.CFSHORTPINYIN, t.CFPINYIN, t.FNAME_L1, "
                + "t.FNAME_L2, t.FDESCRIPTION_L1, t.FDESCRIPTION_L2, t.FDESCRIPTION_L3, t.CFJNBJJ, t.CFDAJJ, "
                + " t.CFKMELCSJID, t.CFKMSLCSJID,  t.CFMNKCS, t.CFKMEQKCS, "
                + " t.CFMNKFS, t.CFKMYQKCS, t.CFKMSQKCS,  t.CFYHJ, t.CFSNQL, "
                + " t.CFWCQL, t.CFTRAINBESPEAKREMARK, t.CFEXAMBESPEAKREMARK, t.LNG, t.LAT, t.DISTRICT, t.CITY, t.PROVINCE,u.name as FCREATENAME,u1.name as FLASTUPDATENAME,u2.FNAME_L2 AS CFTJRNAME,C.FNAME_L2 AS CFCLAZZNAME,Y.FNAME_L2 AS CFTRAINTYPENAME"
                + " ,E1.FNAME_L2 AS CFKMELCSJNAME,E2.FNAME_L2 AS CFKMSLCSJNAME ,A.FNAME_L2 AS cfficompanyName "
                + " ,scs.cfsubject as cfsubject,scs.cfcoachname,scs.CFBEGINTIME,scs.CFENDTIME"
                + "  FROM CT_DS_STUDENT T LEFT JOIN T_E_USER U ON T.FCREATORID=U.FID LEFT JOIN T_E_USER U1 ON T.FLASTUPDATEUSERID=U1.FID LEFT JOIN CT_DS_PERSON  u2 on t.cftjrid=u2.fid left join CT_DS_CLAZZ C ON T.cfclazzid=C.FID"
                + " LEFT JOIN  CT_DS_TRAINTYPE Y ON T.CFTRAINTYPEID=Y.FID LEFT JOIN CT_DS_EXERCISECARPLAN E1 ON E1.FID=T.CFKMELCSJID LEFT JOIN CT_DS_EXERCISECARPLAN E2 ON E2.FID=T.CFKMSLCSJID LEFT JOIN CT_DS_ADMIN A ON T.CFFICOMPANYID=A.FID"
                + " LEFT JOIN ct_ds_sharecarsub scs on scs.cfstudentid = t.fid "
                + " WHERE  t.cfstudentstate != 11 AND t.cfstudentstate != 13 AND t.cfstudentstate != 14  ";
        //+ "and t.TIMINGCHARGE = 1";
        //+ "and scs.CFCOMPUTATIONTIMESTATE = 1";
        if (!"".equals(name)) {
            psql += "AND t.FNAME_L2 LIKE '%" + name + "%' ";
        }
        if (!"".equals(cflxdh)) {
            psql += "AND t.cflxdh LIKE '%" + cflxdh + "%' ";
        }
        psql += "AND (t.CFRXSJ BETWEEN to_date('" + beginTime + "','YYYY/MM/DD') AND to_date('" + endTime + "','YYYY/MM/DD'))";

        int psize = 50; //每页显示数
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = studentService.selectPage(Integer.valueOf(pindex), psql, psize);
        PageBean<Student> pb = new PageBean<>();
        pb.setPc(Integer.valueOf(pindex)); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        Map stu = new HashMap();
        stu.put("sList", (List<Student>) paramMap.get("v_cur"));

        stu.put("pb", (pb.getTr() / pb.getPs()));

        return stu; //每页数据集;

    }

    /**
     * @Author
     * @Date 查询入学登记计时收费学员信息(不分页)
     */
    @SuppressWarnings("unchecked")
    @ResponseBody
    @RequestMapping(value = "/getPage3")
    public List<Student> selectTimingStudent(Student student, HttpServletRequest request) throws Exception {
        String comanyid = ((User) request.getSession().getAttribute("user")).getFcompanyid();
        student.setCfficompanyid(comanyid);
        List<Student> stu = studentService.selectTimingStudent(student);
        return stu;
    }


    /**
     * 取记录总数 dmz 20160921
     *
     * @param psql SQL语句
     * @return Map
     * @throws Exception
     */
    public Map<String, Object> getPageBeanCount(String psql) throws Exception {

        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = studentService.selectPageCount(psql);
        return paramMap;
    }


    /**
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    @ResponseBody
    @RequestMapping(value = "/getPage")
    public Map getPage(String pindex, HttpServletRequest request) throws Exception {
        HttpSession session = request.getSession();
        User user = (User) session.getAttribute("user");                                   //获取已经登录用户数据
        Department department = departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
        String psql = "select to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                + "to_char(t.CFRXSJ,'yyyy-MM-dd ')  cfrxsj,"
                + "to_char(t.CFKMYHERQ,'YYYY-MM-DD HH24:MI:SS')  cfkmyherq,"
                + "to_char(t.CFBIRTHDAY,'YYYY-MM-DD') cfbirthdays,"
                + "to_char(t.CFKMEHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmehgrq,"
                + "to_char(t.CFKMSHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmshgrq,"
                + "to_char(t.CFKMYBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmybjgsj,"
                + "to_char(t.CFKMSBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmsbjgsj,"
                + "to_char(t.CFKMEBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmebjgsj,"
                + "to_char(t.CFKMETLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmetlrq,"
                + "to_char(t.CFKMSTLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmstlrq,"
                + "to_char(t.CFMNKHGSJ,'YYYY-MM-DD HH24:MI:SS') cfmnkhgsj,"
                + "to_char(t.CFMNKBJGRQ,'YYYY-MM-DD HH24:MI:SS') cfmnkbjgrq,"
                + "to_char(t.CFKMEKSQKSJ,'YYYY-MM-DD HH24:MI:SS') cfkmeksqksj,"
                + "to_char(t.CFMNKSJ,'YYYY-MM-DD HH24:MI:SS') cfmnksj ,"
                + "t.FID, t.FNUMBER, t.FSIMPLENAME, t.FCREATORID, t.FLASTUPDATEUSERID, "
                + "t.FCONTROLUNITID, t.CFFICOMPANYID, t.CFNATIONALITYID, t.CFIDCARDNO, t.CFNATION, "
                + "t.CFADDRESS, t.CFLINKADDRESS, t.CFZZZH, t.CFSJHM, t.CFLXDH, t.CFYZBM, t.CFEMAIL, t.CFSLLX, t.CFXZJCXDH, "
                + "t.CFSLZJCXDH, t.CFTJRID, t.CFCLAZZID, t.CFAMOUNT, t.CFLOCAL, t.CFTRAINTYPEID, t.CFREMARK, "
                + "t.CFSEX, t.CFSTUDENTSTATE, t.CFTIMECARD, t.CFRECEIVED, t.CFARREARAGE, t.CFFAVORABLE, t.CFKMYKSS, "
                + " t.CFKMEKSS, t.CFKMSKSS, t.CFDEPARTMENT, t.CFZKZMBH, t.CFDAZT, "
                + "t.CFCFWZ, t.CFDESC, t.CFBXWX, t.CFJSYBH, t.CFBIMUDF0060, "
                + " t.CFBIMUDF0061,  t.CFKMSTLCS, t.CFKMSTLYY, t.CFSHORTPINYIN, t.CFPINYIN, t.FNAME_L1, "
                + "t.FNAME_L2, t.FDESCRIPTION_L1, t.FDESCRIPTION_L2, t.FDESCRIPTION_L3, t.CFJNBJJ, t.CFDAJJ, "
                + " t.CFKMELCSJID, t.CFKMSLCSJID,  t.CFMNKCS, t.CFKMEQKCS, "
                + " t.CFMNKFS, t.CFKMYQKCS, t.CFKMSQKCS,  t.CFYHJ, t.CFSNQL, "
                + " t.CFWCQL, t.CFTRAINBESPEAKREMARK, t.CFEXAMBESPEAKREMARK, t.LNG, t.LAT, t.DISTRICT, t.CITY, t.PROVINCE,u.name as FCREATENAME,u1.name as FLASTUPDATENAME,u2.FNAME_L2 AS CFTJRNAME,C.FNAME_L2 AS CFCLAZZNAME,Y.FNAME_L2 AS CFTRAINTYPENAME "
                + " ,E1.FNAME_L2 AS CFKMELCSJNAME,E2.FNAME_L2 AS CFKMSLCSJNAME "
                + "  FROM CT_DS_STUDENT T LEFT JOIN T_E_USER U ON T.FCREATORID=U.FID LEFT JOIN T_E_USER U1 ON T.FLASTUPDATEUSERID=U1.FID LEFT JOIN CT_DS_PERSON  u2 on t.cftjrid=u2.fid left join CT_DS_CLAZZ C ON T.cfclazzid=C.FID"
                + " LEFT JOIN  CT_DS_TRAINTYPE Y ON T.CFTRAINTYPEID=Y.FID LEFT JOIN CT_DS_EXERCISECARPLAN E1 ON E1.FID=T.CFKMELCSJID LEFT JOIN CT_DS_EXERCISECARPLAN E2 ON E2.FID=T.CFKMSLCSJID "
                + " where t.CFFICOMPANYID= '" + department.getFid() + "'";
        int psize = 15; //每页显示数
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = studentService.selectPage(Integer.valueOf(pindex), psql, psize);
        PageBean<Student> pb = new PageBean<>();
        pb.setPc(Integer.valueOf(pindex)); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        request.setAttribute("pb", pb);
        Map stu = new HashMap();
        stu.put("sList", (List<Student>) paramMap.get("v_cur"));

        return stu; //每页数据集;
    }

    /**
     * @Author lcx
     * @Date 2017/11/16 0016 上午 8:47
     * 档案管理查询学生信息 要求可以报考科目二的人 state == 1为报考科目1的人 以此类推 type 预约或者考试状态 cflcsjid为练车时间id
     * 默认为分车状态
     */
    @SuppressWarnings("unchecked")
    @ResponseBody
    @RequestMapping(value = "/getPageEnrol")
    public Map getPageBeanAsEnrol(HttpServletRequest request, @RequestParam(defaultValue = "", value = "name") String name,
                                  @RequestParam(defaultValue = "", value = "cfsjhm") String cfsjhm, @RequestParam(defaultValue = "0")
                                          String pindex, String beginTime, String endTime, @RequestParam(defaultValue = "fcyy", value = "type")
                                          String type, int state, @RequestParam(defaultValue = "", value = "cflcsjid") String cflcsjid,
                                  @RequestParam(defaultValue = "", value = "cayType") String cayType) throws Exception {
        if (name != null) {
            name = name.trim();
        }
        String psql = "select distinct to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                + "to_char(t.CFRXSJ,'yyyy-MM-dd ')  cfrxsj,"
                + "to_char(t.CFKMYHERQ,'YYYY-MM-DD HH24:MI:SS')  cfkmyherq,"
                + "to_char(t.CFBIRTHDAY,'YYYY-MM-DD') cfbirthdays,"
                + "to_char(t.CFKMEHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmehgrq,"
                + "to_char(t.CFKMSHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmshgrq,"
                + "to_char(t.CFKMYBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmybjgsj,"
                + "to_char(t.CFKMSBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmsbjgsj,"
                + "to_char(t.CFKMEBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmebjgsj,"
                + "to_char(t.CFKMETLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmetlrq,"
                + "to_char(t.CFKMSTLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmstlrq,"
                + "to_char(t.CFMNKHGSJ,'YYYY-MM-DD HH24:MI:SS') cfmnkhgsj,"
                + "to_char(t.CFMNKBJGRQ,'YYYY-MM-DD HH24:MI:SS') cfmnkbjgrq,"
                + "to_char(t.CFKMEKSQKSJ,'YYYY-MM-DD HH24:MI:SS') cfkmeksqksj,"
                + "to_char(t.CFMNKSJ,'YYYY-MM-DD HH24:MI:SS') cfmnksj ,"
                + "t.FID, t.FNUMBER, t.FSIMPLENAME, t.FCREATORID, t.FLASTUPDATEUSERID, "
                + "t.FCONTROLUNITID, t.CFFICOMPANYID, t.CFNATIONALITYID, t.CFIDCARDNO, t.CFNATION, "
                + "t.CFADDRESS, t.CFLINKADDRESS, t.CFZZZH, t.CFSJHM, t.CFLXDH, t.CFYZBM, t.CFEMAIL, t.CFSLLX, t.CFXZJCXDH, "
                + "t.CFSLZJCXDH, t.CFTJRID, t.CFCLAZZID, t.CFAMOUNT, t.CFLOCAL, t.CFTRAINTYPEID, t.CFREMARK, "
                + "t.CFSEX, t.CFSTUDENTSTATE, t.CFTIMECARD, t.CFRECEIVED, t.CFARREARAGE, t.CFFAVORABLE, t.CFKMYKSS, "
                + " t.CFKMEKSS, t.CFKMSKSS, t.CFDEPARTMENT, t.CFZKZMBH, t.CFDAZT, "
                + "t.CFCFWZ, t.CFDESC, t.CFBXWX, t.CFJSYBH, t.CFBIMUDF0060, "
                + " t.CFBIMUDF0061,  t.CFKMSTLCS, t.CFKMSTLYY, t.CFSHORTPINYIN, t.CFPINYIN, t.FNAME_L1, "
                + "t.FNAME_L2, t.FDESCRIPTION_L1, t.FDESCRIPTION_L2, t.FDESCRIPTION_L3, t.CFJNBJJ, t.CFDAJJ, "
                + " t.CFKMELCSJID, t.CFKMSLCSJID,  t.CFMNKCS, t.CFKMEQKCS, "
                + " t.CFMNKFS, t.CFKMYQKCS, t.CFKMSQKCS,  t.CFYHJ, t.CFSNQL, "
                + " t.CFWCQL, t.CFTRAINBESPEAKREMARK, t.CFEXAMBESPEAKREMARK, t.LNG, t.LAT, t.DISTRICT, t.CITY, t.PROVINCE,u.name as FCREATENAME,u1.name as FLASTUPDATENAME,u2.FNAME_L2 AS CFTJRNAME,c.cfvip as cfvip,C.FNAME_L2 AS CFCLAZZNAME,Y.FNAME_L2 AS CFTRAINTYPENAME "
                + " ,E1.FNAME_L2 AS CFKMELCSJNAME,E2.FNAME_L2 AS CFKMSLCSJNAME ,A.FNAME_L2 AS cfficompanyName,su.CFCKBK as cfckbk"
                + "  FROM CT_DS_STUDENT T LEFT JOIN T_E_USER U ON T.FCREATORID=U.FID LEFT JOIN T_E_USER U1 ON T.FLASTUPDATEUSERID=U1.FID LEFT JOIN CT_DS_PERSON  u2 on t.cftjrid=u2.fid left join CT_DS_CLAZZ C ON T.cfclazzid=C.FID left JOIN CT_DS_ADMIN A ON T.cfficompanyid=A.FID"
                + " LEFT JOIN  CT_DS_TRAINTYPE Y ON T.CFTRAINTYPEID=Y.FID LEFT JOIN CT_DS_EXERCISECARPLAN E1 ON E1.FID=T.CFKMELCSJID LEFT JOIN CT_DS_EXERCISECARPLAN E2 ON E2.FID=T.CFKMSLCSJID left join CT_DS_SUBJECTTWOREGENTRY su on su.CFSTUDENTID = t.fid";

        //分车用到的学生列表
        if (type == null || "fc".equals(type)) {
            //科目二分车 科目一通过 科目二未通过,科目三未开始 科目三通过,科目二通过 科目三通过,科目二未通过 科目三通过,科目二未开始 科目二未通过,科目三未通过 科目三未通过，科目二未开始
            if (state == 2) {
                psql += " LEFT JOIN CT_DS_SHARECARSUB f on f.CFSTUDENTID = t.FID " +
                        "WHERE (t.cfstudentstate = 2 or t.cfstudentstate = 5 or t.cfstudentstate = 7 or t.cfstudentstate = 17 or t.cfstudentstate = 19 or t.cfstudentstate = 20 or t.cfstudentstate = 21 ) " +
                        "and t.cfstudentstate != 10 and f.CFSTUDENTID = t.fid and f.CFEXERCISECARPLANI = '" + cflcsjid + "'";
            }
            //科目三分车 科目一通过 科目二通过,科目三未开始 科目二未通过,科目三未开始 科目三通过,科目二通过 科目三未通过,科目二通过 科目二未通过,科目三未通过 科目三未通过，科目二未开始
            if (state == 3) {
                psql += " LEFT JOIN CT_DS_SHARECARSUB f on f.CFSTUDENTID = t.FID " +
                        "WHERE (t.cfstudentstate = 2 or t.cfstudentstate = 4 or t.cfstudentstate = 5 or t.cfstudentstate = 7 or t.cfstudentstate = 8 or t.cfstudentstate = 20 or t.cfstudentstate = 21 ) " +
                        "and t.cfstudentstate != 10 and f.CFSTUDENTID = t.fid and f.CFEXERCISECARPLANI = '" + cflcsjid + "'";
            }

        }
        //分车预约用到的学生列表
        else if (type == null || "fcyy".equals(type)) {
            //科目二分车 科目一通过 科目二未通过 科目三通过
            if (state == 2) {
                psql += " WHERE (t.cfstudentstate = 2 or t.cfstudentstate = 5 or t.cfstudentstate = 7 or t.cfstudentstate = 17 or t.cfstudentstate = 19 or t.cfstudentstate = 20 or t.cfstudentstate = 21 ) " +
                        "and t.cfstudentstate != 10 and t.CFARREARAGE <= 0  and  t.CFARREARAGE is not null";
            }
            //科目三分车 科目一通过 科目二通过 科目三未通过
            if (state == 3) {
                psql += " WHERE (t.cfstudentstate = 2 or t.cfstudentstate = 4 or t.cfstudentstate = 5 or t.cfstudentstate = 7 or t.cfstudentstate = 8 or t.cfstudentstate = 20 or t.cfstudentstate = 21 )" +
                        " and t.cfstudentstate != 10 and t.CFARREARAGE <= 0  and  t.CFARREARAGE is not null";
            }

        }
        //考试预约所用到的学生列表
        else if (type == null || "yy".equals(type)) {
            //科目一预约 已报名 科目一未通过
            if (state == 1) {
                psql += " WHERE (t.cfstudentstate = 1 or t.cfstudentstate = 3 ) and t.cfstudentstate != 10 ";
            }
            //科目二预约 科目二练车中
            if (state == 2) {
                psql += " WHERE (t.cfstudentstate = 5 or t.cfstudentstate = 6 or t.cfstudentstate = 17 or  t.cfstudentstate = 18 or t.cfstudentstate = 20 or t.cfstudentstate = 22 ) " +
                        "and t.cfstudentstate != 10 ";

            }
            //科目三预约 科目三练车中
            if (state == 3) {
                psql += " WHERE (t.cfstudentstate = 9 or t.cfstudentstate = 8 or t.cfstudentstate = 15 or t.cfstudentstate = 16 or t.cfstudentstate = 20 or t.cfstudentstate = 21) " +
                        "and t.cfstudentstate != 10 ";
            }
            if (!"".equals(cayType)) {
                psql += "and t.CFSLZJCXDH ='" + cayType + "'";
            }
        }
        if (name != null && (!"".equals(name))) {
            psql += " AND t.FNAME_L2 LIKE '%" + name + "%' ";
        }
        if (cfsjhm != null && (!"".equals(cfsjhm))) {
            psql += " AND t.cfsjhm LIKE '%" + cfsjhm + "%' ";
        }
        psql += " AND t.cfstudentstate != 11 AND t.cfstudentstate != 13 AND t.cfstudentstate != 14 and t.FCONTROLUNITID= '" + ((User) request.getSession().getAttribute("user")).getFcompanyid() + "'";

        if (!"".equals(beginTime) && !"".equals(endTime)) {
            psql += " AND (t.CFRXSJ BETWEEN to_date('" + beginTime + "','YYYY/MM/DD') AND to_date('" + endTime + "','YYYY/MM/DD'))";
        }

        int psize = 50; //每页显示数
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = studentService.selectPage(Integer.valueOf(pindex), psql, psize);
        PageBean<Student> pb = new PageBean<>();
        pb.setPc(Integer.valueOf(pindex)); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        Map stu = new HashMap();
        stu.put("sList", (List<Student>) paramMap.get("v_cur"));

        stu.put("pb", (pb.getTr() / pb.getPs()));

        return stu; //每页数据集;

    }

    /**
     * 读取身份证信息
     * author zyq
     *
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/readTxtInformation")
    public Student readTxtInformation(HttpServletRequest request) throws IllegalAccessException, IOException, NativeException {
        HttpSession session = request.getSession();
        User user = (User) session.getAttribute("user");
        if (systemParameterService.querySysetmParamenterByFnameL2(user.getFcompanyid(), "身份证dll路径").isEmpty()) {

        } else {
            SystemParameter systemParameter = systemParameterService.querySysetmParamenterByFnameL2(user.getFcompanyid(), "身份证dll路径").get(0);
            int i = ReadTxt.CVR_InitComm(systemParameter.getCfnumbers().intValue(), systemParameter.getFdescriptionL2().toString());   //调用读取DLL，初始化设备
            ReadTxt.CVR_Authenticate(systemParameter.getFdescriptionL2().toString());
            ReadTxt.CVR_Read_Content(4, systemParameter.getFdescriptionL2().toString());                                        //读取身份证
            Student student = ReadTxt.readTxtInformation(systemParameter.getFdescriptionL2().toString());
            /*   ReadTxt.readIdCardImage(response);*/
            session.setAttribute("path", systemParameter.getFdescriptionL2().toString());
            return student;
        }
        return null;
    }

    /**
     * 读取图片
     *
     * @param response
     * @param request
     * @throws IOException
     * @author zyq
     */
    @RequestMapping(value = "/readTxtImage")
    public void readIdCardImage(HttpServletResponse response, HttpServletRequest request, String imageData) throws IOException {
        String flag = request.getParameter("c");
        if (flag == null) {
            request.getSession().setAttribute("imageData", imageData);
        } else {

            imageData = (String) request.getSession().getAttribute("imageData");
            if (imageData != null) {
                BASE64Decoder decoder = new BASE64Decoder();
                // Base64解码
                byte[] image = decoder.decodeBuffer(imageData);
                for (int i = 0; i < image.length; ++i) {
                    if (image[i] < 0) {// 调整异常数据
                        image[i] += 256;
                    }
                }
                InputStream buffin = new ByteArrayInputStream(image);            //读取输入流
                BufferedImage images = ImageIO.read(buffin);
                OutputStream os = response.getOutputStream();
                ImageIO.write(images, "PNG", os);                           //向页面输出字节数组
                os.flush();
                os.close();
            /*InputStream in = new ByteArrayInputStream(image);

            File file=new File("d://test.bmp");//可以是任何图片格式.jpg,.png等
            FileOutputStream fos=new FileOutputStream(file);

            byte[] b = new byte[1024];
            int nRead = 0;
            while ((nRead = in.read(b)) != -1) {
                fos.write(b, 0, nRead);
            }
            fos.flush();
            fos.close();
            in.close();*/
            } else {

            }
        }
    }

}
